package com.wuye.repository;

import com.wuye.model.Menu;
import com.wuye.model.MenuDetail;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.List;

public interface MenuRepository extends JpaRepository<Menu, String>, PagingAndSortingRepository<Menu, String> {
    Menu findByMenuId(String menuId);

    @Query(nativeQuery = true, value = "select menu.* from wuye_menu menu LEFT JOIN wuye_menu_activity sc on menu.menu_id=sc.menu_id where sc.for_sale=1 order by convert(menu.item_type using gbk) asc , convert(menu.item_name using gbk) asc ")
    List<Menu> findAllOnSale();

    @Query(nativeQuery = true,value="select wm.menu_id,wm.image_file,wm.item_name,wm.item_description,wm.item_type,wm.item_unit,wm.item_price,wa.for_sale,wa.store_num from wuye_menu wm LEFT JOIN wuye_menu_activity wa on wm.menu_id = wa.menu_id order by convert(wm.item_type using gbk) asc , convert(wm.item_name using gbk) asc")
    List<Object[]> findAllByItemNameDesc();

    @Query(nativeQuery = true, value = "select menu.* from wuye_menu menu order by convert(menu.item_type using gbk) asc , convert(menu.item_name using gbk) asc limit ?1,?2")
    List<Menu> findAllOrderByItemTypeDesc();

    @Query(nativeQuery = true, value = "select count(*) from wuye_menu where item_type=?1")
    int sums(String itemTypes);

    @Query(nativeQuery = true, value = "select wm.menu_id,wm.image_file,wm.item_name,wm.item_description,wm.item_type,wm.item_unit,wm.item_price,wa.for_sale,wa.store_num from wuye_menu wm LEFT JOIN wuye_menu_activity wa on wm.menu_id = wa.menu_id where wm.item_type = ?1 order by convert(wm.item_type using gbk) asc , convert(wm.item_name using gbk) asc")
    List<Object[]> findMenus(String itemType);

    @Query(nativeQuery = true, value = "select wm.menu_id,wm.image_file,wm.item_name,wm.item_description,wm.item_type,wm.item_unit,wm.item_price,wa.for_sale,wa.store_num from wuye_menu wm LEFT JOIN wuye_menu_activity wa on wm.menu_id = wa.menu_id  where wm.item_name like ?1")
    List<Object[]> findItemNameOrderByItemTypeDesc(String itemName);

    @Query(nativeQuery = true,value="select wm.menu_id,wm.image_file,wm.item_name,wm.item_description,wm.item_type,wm.item_unit,wm.item_price,wa.for_sale,wa.store_num from wuye_menu wm LEFT JOIN wuye_menu_activity wa on wm.menu_id = wa.menu_id where wm.item_type = ?1 order by convert(wm.item_type using gbk) asc , convert(wm.item_name using gbk) asc")
    List<Object[]> findByItemTypeOrderByItemTypeDesc(String itemType);

    @Query(nativeQuery = true,value="select wm.menu_id,wm.image_file,wm.item_name,wm.item_description,wm.item_type,wm.item_unit,wm.item_price,wa.for_sale,wa.store_num from wuye_menu wm LEFT JOIN wuye_menu_activity wa on wm.menu_id = wa.menu_id where wm.item_name = ?1 and wm.item_type = ?2 order by convert(wm.item_type using gbk) asc , convert(wm.item_name using gbk) asc")
    List<Object[]> findByItemNameAndItemTypeOrderByItemTypeDesc(String itemName, String itemType);

    @Query(nativeQuery = true, value = "select count(*) from wuye_menu where item_name like ?1")
    int sumssbyItems(String itemName);

    @Query(nativeQuery = true, value = "select count(*) from wuye_menu")
    int sumss();

    @Query(nativeQuery = true, value = "select count(*) from wuye_menu where item_name like ?1 and item_type = ?2")
    int sumsa(String names, String types);


    @Query(nativeQuery = true,value="select wm.menu_id,wm.image_file,wm.item_name,wm.item_description,wm.item_type,wm.item_unit,wm.item_price,wa.for_sale,wa.store_num from wuye_menu wm LEFT JOIN wuye_menu_activity wa on wm.menu_id = wa.menu_id order by convert(wm.item_type using gbk) asc , convert(wm.item_name using gbk) asc limit ?1,?2")
    List<Object[]> findAllOrderByItemTypesDesc(int beginSize, int endSize);

    @Query(nativeQuery = true, value = "select wm.menu_id,wm.image_file,wm.item_name,wm.item_description,wm.item_type,wm.item_unit,wm.item_price,wa.for_sale,wa.store_num from wuye_menu wm LEFT JOIN wuye_menu_activity wa on wm.menu_id = wa.menu_id where wm.item_type = ?1 order by convert(wm.item_type using gbk) asc , convert(wm.item_name using gbk) asc limit ?2,?3")
     List<Object[]> findByItemTypeOrderByItemTypesDesc(String itemType, int beginSize, int endSize);

    @Query(nativeQuery = true, value = "select wm.menu_id,wm.image_file,wm.item_name,wm.item_description,wm.item_type,wm.item_unit,wm.item_price,wa.for_sale,wa.store_num from wuye_menu wm LEFT JOIN wuye_menu_activity wa on wm.menu_id = wa.menu_id where wm.item_name like ?1 and wm.item_type = ?2 order by convert(wm.item_type using gbk) asc , convert(wm.item_name using gbk) asc limit ?3,?4")
    List<Object[]> findByItemNameAndItemTypeOrderByItemTypesDesc(String itemName, String itemType, int beginSize, int endSize);


    /*@Query(nativeQuery = true, value = "select * from wuye_menu menu LEFT JOIN wuye_menu_schedule sc on menu.menu_id=sc.menu_id where sc.week_day = WEEKDAY(NOW()) and sc.suit in (0,1) and sc.`week` in (0)")
    public List<Menu> finTodayMenu();*/
    @Query(nativeQuery = true,value="select wm.menu_id,wm.image_file,wm.item_name,wm.item_description,wm.item_type,wm.item_unit,wm.item_price,wa.for_sale,wa.store_num from wuye_menu wm LEFT JOIN wuye_menu_activity wa on wm.menu_id = wa.menu_id")
    List<Object[]> findMenuByJoins();

    //查询商品信息
    @Query(nativeQuery = true,value="select wm.menu_id,wm.image_file,wm.item_name,wm.item_description,wm.item_type,wm.item_unit,wm.item_price,wa.for_sale,wa.store_num from wuye_menu wm LEFT JOIN wuye_menu_activity wa on wm.menu_id = wa.menu_id where wm.menu_id = ?1")
    List<Object[]> findOneDetail(String menuId);
}